from nicegui import ui,app
import sqlite3
from PIL import Image
import numpy as np
import datetime
import random
import requests
sq=sqlite3.connect('RD.db')
c=sq.cursor()
def db_create():
    #用户列表
    c.execute('''create table if not exists user(
    id integer primary key autoincrement,
    logid text,
    name text,
    password text,
    iconpath text,
    role text)''')
    #作者列表 姓名 头像路径 简介 国家
    c.execute('''create table if not exists author(
    id integer primary key autoincrement,
    name text,
    iconpath text,
    intro text,
    country text)''')
    #评论列表 书籍id 评论内容 评论时间 评论人id
    c.execute('''create table if not exists comment(
    id integer primary key autoincrement,
    bid integer,
    content text,
    time text,
    uid integer)''')
    #书籍列表  书名 作者id 翻译者id 出版社 出版年份 图片路径 评分 简介
    c.execute('''create table if not exists book(
    id integer primary key autoincrement,
    name text,
    aid integer,
    tid integer,
    press text,
    pressdate text,
    imgpath text,
    score text,
    intro text)''')
    #阅读记录表  用户id 书籍id 阅读状态 开始时间 结束时间
    c.execute('''create table if not exists read(
    id integer primary key autoincrement,
    uid integer,
    bid integer,
    state text,
    stime text,
    etime text)''')
    #日志表 用户id 操作 时间
    c.execute('''create table if not exists log(
    id integer primary key autoincrement,
    uid integer,
    op text,
    time text)''')
    sq.commit()
db_create()
def db_add():
    #用户列表创建一条记录
    #c.execute('insert into user values(?,?,?,?,?,?)',(None,"ad1","管理员","123","pic/user.png","管理员"))
    #作者列表创建一条记录
    #c.execute('insert into author values(?,?,?,?,?)',(None,"Eric Matthes","a1.ico","Python之父","美国"))
    #阅读记录表创建一条记录
    #c.execute('insert into read values(?,?,?,?,?,?)',(None,1,2,"想读","2022-12-12 12:12:12","2022-12-12 12:12:12"))
    #c.execute('insert into read values(?,?,?,?,?,?)',(None,1,3,"想读","2022-12-12 12:12:12","2022-12-12 12:12:12"))
    #c.execute('insert into read values(?,?,?,?,?,?)',(None,1,4,"已读","2022-12-12 12:12:12","2022-12-12 12:12:12"))
    #c.execute('insert into read values(?,?,?,?,?,?)',(None,1,5,"在读","2022-12-12 12:12:12","2022-12-12 12:12:12"))
    #c.execute('insert into read values(?,?,?,?,?,?)',(None,1,7,"","2022-12-12 12:12:12","2022-12-12 12:12:12"))
    #评论列表创建一条记录
    #c.execute('insert into comment values(?,?,?,?,?)',(None,1,"这本书一般",datetime.datetime.now(),1))
    #c.execute('insert into comment values(?,?,?,?,?)',(None,2,"这本书还可以",datetime.datetime.now(),1))
    #c.execute('insert into comment values(?,?,?,?,?)',(None,3,"这本书也许好",datetime.datetime.now(),1))
    #c.execute('insert into comment values(?,?,?,?,?)',(None,4,"这本书挺好",datetime.datetime.now(),1))
    #c.execute('insert into comment values(?,?,?,?,?)',(None,5,"这本书说不定可以",datetime.datetime.now(),1))
    sq.commit()
db_add()
###################################################全局变量####################################################################
prime_color="#55b5f5"
FOOT_COLOR="#95859C"
PIC_HIGHT=[180,185,250,300]#全局 pic高度方便修改
#第一个数字为书架页的高度
#第二个数字为详情页的高度
#第三个为首页推荐页的高度

####################################################终极页面####################################################################
@ui.page("/deep")
def p_deep():
        if app.storage.user.get("role")!="管理员" or app.storage.user.get("role") is None:
            ui.navigate.to("/login")
        else:
            with ui.row().style("width:100%"):
                ui.label("管理员").on('click',lambda:ui.navigate.to("/user1"))
            footer()
####################################################收录页面####################################################################
@ui.page("/include")
def p_include():
        def inc():
            if len(bname.value)!=0 and len(baid.value)!=0 and len(bpub.value)!=0 and len(bpdate.value)!=0 and len(bpic.value)!=0 and len(brate.value)!=0 and len(bintro.value)!=0:
                #在book表中根据书名，出版社，出版时间查询书籍是否存在
                c.execute('select * from book where name=? and press=? and pressdate=?',(bname.value,bpub.value,bpdate.value))
                book=c.fetchone()
                if book:
                    ui.notify("该书籍已经被收录")
                else:
                    #先处理译者id
                    if len(btid.value)==0:
                        tid=0
                    else:
                        tt=btid.value
                        #在author表中根据作者名查询作者是否存在
                        c.execute('select * from author where name=?',(tt,))
                        ti=c.fetchone()
                        if ti:
                            tid=ti[0]
                        else:
                            #在author表中插入一个作者
                            c.execute('insert into author values(?,?,?,?,?)',(None,tt,"pic/author.png","暂无","未知"))
                            sq.commit()
                            #获取新插入的作者id
                            c.execute('select * from author where name=?',(tt,))
                            tid=c.fetchone()[0]
                    #开始处理作者id
                    author=baid.value
                    #在author表中根据作者名查询作者是否存在
                    c.execute('select * from author where name=?',(author,))
                    ti=c.fetchone()
                    if ti:
                        aid=ti[0]
                    else:
                        #在author表中插入一个作者
                        c.execute('insert into author values(?,?,?,?,?)',(None,author,"pic/author.png","暂无","未知"))
                        sq.commit()
                        #获取新插入的作者id
                        c.execute('select * from author where name=?',(author,))
                        aid=c.fetchone()[0]
                    #先在book表中插入一条记录
                    c.execute('insert into book values(?,?,?,?,?,?,?,?,?)',(None,bname.value,aid,tid,bpub.value,bpdate.value,bpic.value,brate.value,bintro.value))
                    sq.commit()
                    #再获取刚刚插入的书籍id
                    c.execute('select * from book where name=? and press=? and pressdate=?',(bname.value,bpub.value,bpdate.value))
                    bid=c.fetchone()[0]
                    #处理封面图片，保存bpic.value到pic文件夹下，命名为bid+bid.png
                    r = requests.get(bpic.value)
                    open(f'pic/bid{bid}.png', 'wb').write(r.content)
                    #更新book表中的图片路径
                    c.execute('update book set imgpath=? where id=?',(f'pic/bid{bid}.png',bid))
                    sq.commit()
                    ui.notify("收录成功")
            else:
                ui.notify("请填写完整")
        if app.storage.user.get("role")!="管理员" or app.storage.user.get("role") is None:
            ui.navigate.to("/login")
        else:
            bname=ui.input("书名",placeholder="请输入书名")
            baid=ui.input("作者",placeholder="请输入作者")
            btid=ui.input("译者",placeholder="请输入译者")
            bpub=ui.input("出版社",placeholder="请输入出版社名称")
            bpdate=ui.input("出版日期",placeholder="请输入出版日期")
            bpic=ui.input("封面",placeholder="请输入封面图片链接")
            brate=ui.input("评分",placeholder="请输入评分")
            bintro=ui.input("简介",placeholder="请输入书籍简介")
            ui.button("开始收录",on_click=inc)
            footer()
###################################################登录页面####################################################################
@ui.page("/login")#已完成
def login():
        async def check():
            uid=ur.value
            pwd=pw.value
            c.execute('select * from user where logid=? and password=?',(uid,pwd))
            user=c.fetchone()
            if user:
                ui.notify(user[2]+"欢迎回来！")
                ui.notify(user[2]+"欢迎回来！")
                ui.notify(user[2]+"欢迎回来！")
                ui.notify(user[2]+"欢迎回来！")
                ui.navigate.to("/")
                app.storage.user['uid']=user[0]
                app.storage.user['name']=user[2]
                app.storage.user['icon']=user[4]
                app.storage.user['role']=user[5]
            else:
                ui.notify("账号或密码错误")
        ui.colors(primary=prime_color)
        with ui.column().style("display:grid;place-items:center;width:100%;"):
            ui.image("pic/book.png").style("width:100%;height:100%;")
            ur=ui.input(placeholder="请输入账号").style("width:100%;")
            pw=ui.input(placeholder="请输入密码").style("width:100%;")
            ui.button("登录",on_click=check).style("width:100%;")
###################################################作者页面####################################################################
@ui.page("/author{aid}")#已完成
def p_author(aid):
    if app.storage.user.get("role") is None or app.storage.user.get("role")=="":
        ui.navigate.to("/login")
    else:
        c.execute('select * from author where id=?',(int(aid),))
        author=c.fetchone()
        main_color,ts,fs=get_dominant_color(author[2])
        ui.query("body").style(main_color)
        with ui.row().style("width:100%"):
            with ui.card().style("width:30%;padding:0px"):
                ui.image(author[2]).style("height:100%;width:100%")
            with ui.column().style("width:62%;padding:0px"):
                ui.label(author[1]).style("color:grey;font-size:30px")
                ui.label("国籍:"+author[4]).style("color:grey;")
            ui.label("简介："+author[3]).style("color:grey;")

        ui.label("作品:").style("color:grey;font-size:20px")
        c.execute('select * from book where aid =? or tid =?',(aid,aid))
        books=c.fetchall()
        groups=[books[i:i+3]for i in range(0, len(books), 3)]
        for group in groups:
                with ui.row().style("width:100%;"):
                    for book in group:
                        with ui.column().style("width:30%;"):
                            with ui.card().style("width:100%;height:{}px;padding:0px".format(PIC_HIGHT[0])):
                                img_click=lambda bid=book[0]:ui.navigate.to(f"/detail{bid}")
                                ui.image(book[6]).style("width: 100%; height: 100%; object-fit: cover;").on('click',img_click)
                            ui.label(book[1])
        footer()
###################################################用户页面####################################################################
@ui.page("/user{uid}")#已完成
def p_user(uid):
    def fresh_chart(y):
        for i in datas:
            datas[i]=0
        for info in read_infos:
            if int(info[0])==y:
                datas[int(info[1])-1]+=1
        read_chart.update()
        yl.text="{}年度阅读统计".format(str(y))
        yl.update()
    if app.storage.user.get("role") is None or app.storage.user.get("role")=="":
        ui.navigate.to("/login")
    else:
        def pre_y():
                    local_year=app.storage.user.get("year")
                    local_year-=1
                    app.storage.user["year"]=local_year
                    fresh_chart(local_year)
        def next_y():
                    local_year=app.storage.user.get("year")
                    local_year+=1
                    app.storage.user["year"]=local_year
                    fresh_chart(local_year)
        local_year=datetime.datetime.now().year
        app.storage.user["year"]=local_year
        read_infos=[]
        c.execute('select * from user where id=?',(int(uid),))
        user=c.fetchone()
        main_color,fs,ts=get_dominant_color(user[4])
        ui.query("body").style(main_color)
        with ui.row().style("width:100%"):
            with ui.card().style("width:30%;padding:0px"):
                ui.image(user[4]).style("height:100%;width:100%")
            with ui.column().style("width:62%;padding:0px"):
                ui.label(user[2]).style("color:grey;font-size:20px")
                ui.label("账号:"+user[1]).style("color:grey;")
                c.execute('select * from read where uid=?',(uid,))
                reads=c.fetchall()
                WANT=0
                ONGOING=0
                DONE=0
                COMM=0
                for read in reads:
                    if read[3]=="想读":
                        WANT+=1
                    elif read[3]=="在读":
                        ONGOING+=1
                    elif read[3]=="已读":
                        DONE+=1
                        year,month,day=read[5].split("/")
                        read_infos.append([year,month])
                c.execute('select * from comment where uid=?',(uid,))
                COMM=len(c.fetchall())
                with ui.row().style("width:100%"):
                    ui.label("想读:"+str(WANT)).style("color:grey;")
                    ui.label("在读:"+str(ONGOING)).style("color:grey;")
                    ui.label("已读:"+str(DONE)).style("color:grey;")
                    ui.label("评记："+str(COMM)).style("color:grey;")
            yl=ui.label("{}年度阅读统计".format(str(local_year))).style("color:grey;font-size:20px")
            datas=[0,0,0,0,0,0,0,0,0,0,0,0]
            read_chart=ui.echart({
                        "title": {"text": "阅读统计By月份"},
                        "tooltip": {
                            "trigger": 'axis',
                            "axisPointer": {
                                "type": 'shadow'
                                }
                            },
                        "grid": {
                            "left": '3%',
                            "right": '4%',
                            "bottom": '3%',
                            "containLabel": True
                            },
                        "xAxis": [
                            {
                                "type": 'category',
                                "data": ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun','Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec',],
                                "axisTick": {
                                    "alignWithLabel": True
                                    },

                                }
                            ],
                            "yAxis": [
                                {
                                    "type": 'value'
                                }
                            ],
                            "series": [
                                {
                                    "name": '已读',
                                    'type': 'bar',
                                    "barWidth": '60%',
                                    'data': datas
                                }
                            ]
                    }
                    )
            fresh_chart(local_year)
            with ui.row().style("width:100%"):
                ui.button("查看上一年",on_click=pre_y)
                ui.button("查看下一年",on_click=next_y)
        ui.label("最近在读").style("font-size:30px")
        with ui.row().style("width:100%"):
            c.execute('select * from read where uid=? order by id desc limit 3',(uid,))
            reads=c.fetchall()
            c.execute('select * from book')
            books=c.fetchall()
            group=[]
            for read in reads:
                for book in books:
                    if read[2]==book[0]:
                        group.append(book)
            with ui.row().style("width:100%;"):
                    for book in group:
                        with ui.column().style("width:30%;"):
                            with ui.card().style("width:100%;height:40%;padding:0px"):
                                img_click=lambda bid=book[0]:ui.navigate.to(f"/detail{bid}")
                                ui.image(book[6]).style("width:100%;height:{}px".format(PIC_HIGHT[0])).on('click',img_click)
                            ui.label(book[1])
        footer()
###################################################详情页面####################################################################
@ui.page("/detail{bid}")#已完成
def p_detail(bid):
    def up_comm():
        comm=comms.value
        if len(comm)!=0:
            c.execute('insert into comment (bid,content,time,uid) values(?,?,?,?)',(bid,comm,str(datetime.datetime.now().strftime("%Y/%m/%d-%H:%M:%S")),app.storage.user["uid"]))
            sq.commit()
            ui.navigate.to(f"/detail{bid}")
    def want():
        c.execute('update read set state="想读", stime=?,etime=? where uid=? and bid=?', ("NA","NA", uid, bid))
        sq.commit()
        ui.navigate.to(f"/detail{bid}")
    def ongoing():
        c.execute('update read set state="在读", stime=? where uid=? and bid=?', (str(datetime.datetime.now().strftime("%Y/%m/%d")), uid, bid))
        sq.commit()
        ui.navigate.to(f"/detail{bid}")
    def done():
        uid = c.execute('select id from user where name=?', (app.storage.user.get("name"),)).fetchone()[0]
        c.execute('update read set state="已读", etime=? where uid=? and bid=?', (str(datetime.datetime.now().strftime("%Y/%m/%d")), uid, bid))
        sq.commit()
        ui.navigate.to(f"/detail{bid}")
    def wantA():
        c.execute('insert into read (uid,bid,state,stime,etime) values(?,?,?,?,?)',(uid,bid,"想读","NA","NA"))
        sq.commit()
        ui.navigate.to(f"/detail{bid}")
    def ongoingA():
        c.execute('insert into read (uid,bid,state,stime,etime) values(?,?,?,?,?)',(uid,bid,"在读",str(datetime.datetime.now().strftime("%Y/%m/%d")),"NA"))
        sq.commit()
        ui.navigate.to(f"/detail{bid}")
    def doneA():
        c.execute('insert into read (uid,bid,state,stime,etime) values(?,?,?,?,?)',(uid,bid,"已读","NA",str(datetime.datetime.now().strftime("%Y/%m/%d"))))
        sq.commit()
        ui.navigate.to(f"/detail{bid}")
    if app.storage.user.get("role") is None or app.storage.user.get("role")=="":
        ui.navigate.to("/login")
    else:
        c.execute('select * from book where id=?',(int(bid),))
        book=c.fetchone()
        book_pic=book[6]
        main_color,ts,fs=get_dominant_color(book_pic)
        ui.query("body").style(main_color)
        with ui.row().style("width:100%"):
            img=Image.open(book_pic)
            w,h=img.size
            with ui.card().style("width:{}px;height:{}px;padding:0px;".format((w/h)*PIC_HIGHT[1],PIC_HIGHT[1])):
                ui.image(book_pic).style("height:100%;width:100%")
            with ui.column().style("width:200px;padding:0px"):
                ui.label(book[1]).style(ts)
                with ui.row().style("width:100%"):
                    c.execute('select * from author where id=?',(book[2],))
                    author=c.fetchone()
                    img_click=lambda aid=book[2]:ui.navigate.to(f"/author{aid}")
                    ui.label("作者:"+author[1]).style(fs).on('click',img_click)#进入作者详情页面
                    if book[3]!=0:
                        c.execute('select * from author where id=?',(book[3],))
                        author=c.fetchone()
                        img_click=lambda aid=book[3]:ui.navigate.to(f"/author{aid}")
                        ui.label("译者:"+author[1]).style(fs).on('click',img_click)#进入译者详情页面
                ui.label("出版社:"+book[4]).style(fs)
                ui.label("出版时间:"+book[5]).style(fs)
                ui.label("评分:"+book[7]+"分").style(fs)
        with ui.row().style("width:280px;align_items:center;padding:0px;margin:0px"):
                    #在阅读记录表中查找用户id为1且书籍id为bid的记录，如果存在，则显示相应的状态，否则显示"想读"
                    uid = c.execute('select id from user where name=?', (app.storage.user.get("name"),)).fetchone()[0]
                    c.execute('select * from read where uid=? and bid=?', (uid, bid))
                    read=c.fetchone()
                    if read:
                        if read[3]=="想读":
                            ui.chip('已想读', icon='cast', color='grey').style("margin:0px;width:80px")
                            ui.chip('在读', icon='book', color=prime_color,on_click=ongoing).style("margin:0px;width:80px")
                            ui.chip('已读', icon='star', color=prime_color,on_click=done).style("margin:0px;width:80px")
                        elif read[3]=="在读":
                            ui.chip('想读', icon='cast', color=prime_color,on_click=want).style("margin:0px;width:80px")
                            ui.chip('已在读', icon='book', color='grey').style("margin:0px;width:80px")
                            ui.chip('已读', icon='star', color=prime_color,on_click=done).style("margin:0px;width:80px")
                        elif read[3]=="已读":
                            ui.chip('已读完', icon='star', color='grey').style("margin:0px;width:200px")
                        else:
                            ui.chip('想读', icon='cast', color=prime_color,on_click=want).style("margin:0px;width:80px")
                            ui.chip('在读', icon='book', color=prime_color,on_click=ongoing).style("margin:0px;width:80px")
                            ui.chip('已读', icon='star', color=prime_color,on_click=done).style("margin:0px;width:80px")
                    else:
                        ui.chip('想读', icon='cast', color=prime_color,on_click=wantA).style("margin:2px;width:28%")
                        ui.chip('在读', icon='book', color=prime_color,on_click=ongoingA).style("margin:2px;width:28%")
                        ui.chip('已读', icon='star', color=prime_color,on_click=doneA).style("margin:2px;width:28%")
        ui.label("简介："+book[8])
        with ui.card().style("width: 100%; padding: 0px;"):
            with ui.row().style("width: 100%; height: 135px; align-items: center; justify-content: space-between; padding: 0px;"):
                comms = ui.textarea(placeholder="  输入评记...").style("width: calc(100% - 120px); font-size: 20px; color: white; padding: 0px; margin: 0px;")
                ui.button("提交", on_click=up_comm).style("height: 135px; width:100px;font-size: 20px; color: white;")
        ui.label("历史评记：")
        with ui.row().style("width:100%;align_items:center;padding:0px"):
            #在评论列表中根据书籍id查找所有评论，并将其显示在页面上
            c.execute('select * from comment where bid=?',(int(bid),))
            comments=c.fetchall()
            for comm in comments:
                img_click=lambda uid=comm[4]:ui.navigate.to(f"/user{uid}")
                #查询用户表，根据评论者的id查找对应的iconpath字段
                c.execute('select * from user where id=?',(comm[4],))
                user=c.fetchone()
                with ui.row().style("width:100%;padding:5px;margin:1px;background-color:rgba(255,255,255,0.1)"):
                    with ui.avatar().on('click',img_click):#进入评论者详情页面
                        ui.image(user[4])
                    with ui.column().style("padding:0px;width:300px"):
                        ui.label(comm[2]).style("width:100%;white-space: normal; word-wrap: break-word;")
                        ui.label(comm[3]).style("color:grey")
        footer()
###################################################欢迎页面####################################################################
@ui.page("/")#已完成
def p_main():
    def add_read(bid):
        c.execute('insert into read (uid,bid,state,stime,etime) values(?,?,?,?,?)',(app.storage.user.get("uid"),bid,"","NA","NA"))
        sq.commit()
        ui.navigate.to(f"/detail{bid}")
    def refer():
        show_list=[]
        c.execute('select * from book')
        books=c.fetchall()
        c.execute('select * from read')
        reads=c.fetchall()
        for book in books:
            ifread=False
            for read in reads:
                if read[2]==book[0]:
                    ifread=True
                    break
            if not ifread:
                show_list.append(book)
        rand_num=random.randint(0,len(show_list)-1)
        refers=show_list[rand_num]
        bid=refers[0]        
        with ui.card().style("width:100%;padding:0px;height:{}px".format(PIC_HIGHT[2])):
            with ui.row().style("width: 100%; display: flex; justify-content: space-between; align-items: center;"):
                with ui.column().style("flex: 0 0 auto;"):
                    book_pic=refers[6]
                    img=Image.open(book_pic)
                    w,h=img.size
                    pic_w=(w/h)*PIC_HIGHT[2]
                    lw=130#label_width
                    ui.image(book_pic).style("height:{}px;width:{}px".format(PIC_HIGHT[2],pic_w))
                with ui.column().style("flex: 1 1 auto; display: flex; justify-content: center; align-items: center;padding:0px"):
                    ui.label(refers[1]).style("width:{}px;white-space: normal; word-wrap: break-word;color:grey;font-size:20px".format(lw))
                    c.execute('select * from author where id=?',(refers[2],))
                    author=c.fetchone()
                    ui.label("作者:"+author[1]).style("width:{}px;white-space: normal; word-wrap: break-word;color:grey;".format(lw))
                    if refers[3]!=0:
                        c.execute('select * from author where id=?',(refers[3],))
                        author=c.fetchone()
                        ui.label("译者:"+author[1]).style("width:{}px;white-space: normal; word-wrap: break-word;color:grey;".format(lw))
                    ui.label("出版社:"+book[4]).style("width:{}px;white-space: normal; word-wrap: break-word;color:grey;".format(lw))
                    ui.label("出版时间:"+book[5]).style("width:{}px;white-space: normal; word-wrap: break-word;color:grey;".format(lw))
                    ui.label("评分:"+book[7]+"分").style("width:{}px;white-space: normal; word-wrap: break-word;color:grey;".format(lw))
                with ui.column().style("flex: 0 0 auto"):
                    #button右对齐
                    ui.button('''加入书架''',on_click=lambda:add_read(bid)).style("height:{}px;width:50px;font-size:20px;color:white".format(PIC_HIGHT[2]))
        ui.label("简介："+refers[8])
    def search():
        keyinfo=keys.value
        if len(keyinfo)!=0:
            c.execute('select * from book where name like ?',('%'+keyinfo+'%',))
            books=c.fetchall()
            if len(books)==0:
                ui.notify("没有找到相关书籍")
            else:
                ui.notify("找到{}本书".format(len(books)))
                c.execute('select * from read where uid=? order by id desc limit 99999',(app.storage.user.get("uid"),))
                readlist=c.fetchall()
                groups=[books[i:i+3]for i in range(0, len(books), 3)]
                for group in groups:
                    with ui.row().style("width:100%;"):
                        for book in group:
                            with ui.column().style("width:31%;"):
                                with ui.card().style("width:100%;height:160px;padding:0px"):
                                    img_click=lambda bid=book[0]:ui.navigate.to(f"/detail{bid}")
                                    with ui.image(book[6]).style("width:100%").on('click',img_click):
                                        for read in readlist:
                                            if read[2]==book[0]:
                                                state=read[3]
                                                if state=="想读":
                                                    ui.badge("想读",color='pink').props('floating').style("padding:8px")
                                                if state=="已读":
                                                    ui.badge("已读",color='blue').props('floating').style("padding:8px")
                                                if state=="在读":
                                                    ui.badge("在读",color='green').props('floating').style("padding:8px")
                                ui.label(book[1])
        else:
            ui.notify("请输入关键词")
    if app.storage.user.get("role") is None or app.storage.user.get("role")=="":
        ui.navigate.to("/login")
    else:
        with ui.card().style("width:100%;background:#fff;overflow:auto"):
            keys=ui.input(placeholder="请输入书名关键词").style("width:100%;")
            ui.button("搜索",on_click=search).style("width:100%;")
        ui.label("推荐阅读").style("font-size:30px")
        refer()
        footer()
###################################################书架页面####################################################################
@ui.page("/store")#已完成
def p_store():
    imgs=[]
    def fresh():
        #在书籍列表中查找所有书籍，并将其显示在页面上,第一次加载只加载前九个项目的图片，随着页面滑动，依次加载其他图片
        c.execute('select * from book')
        booklist=c.fetchall()
        c.execute('select * from read where uid=? order by id desc limit 99999',(app.storage.user.get("uid"),))
        readlist=c.fetchall()
        groups=[readlist[i:i+3]for i in range(0, len(readlist), 3)]
        for group in groups:
            with ui.row().style("width:100%;"):
                for book in group:
                    bid=book[2]
                    bookname=""
                    for b in booklist:
                        if bid==b[0]:
                            bookname=b[1]
                    with ui.column().style("width:30%;"):
                        with ui.card().style("width:100%;height:{}px;padding:0px".format(PIC_HIGHT[0])):
                            img_click=lambda bid=book[2]:ui.navigate.to(f"/detail{bid}")
                            with ui.image().style("width: 100%; height: 100%; object-fit: cover;").on('click',img_click) as img:
                                if book[3]=="想读":
                                    ui.badge("想读",color='pink').props('floating').style("padding:8px")
                                if book[3]=="已读":
                                    ui.badge("已读",color='blue').props('floating').style("padding:8px")
                                if book[3]=="在读":
                                    ui.badge("在读",color='green').props('floating').style("padding:8px")
                        ui.label(bookname)
                        imgs.append(img)
                
        ui.label("")
        ui.label("")
        ui.label("")
    if app.storage.user.get("role") is None or app.storage.user.get("role")=="":
        ui.navigate.to("/login")
    else:
        fresh()
        c.execute('select * from read where uid=? order by id desc limit 99999',(app.storage.user.get("uid"),))
        readlist=c.fetchall()
        c.execute('select * from book')
        booklist=c.fetchall()
        for i in range(len(readlist)):
            for j in range(len(booklist)):
                if readlist[i][2] == booklist[j][0]:
                    imgs[i].set_source(booklist[j][6])
                    imgs[i].update()
        footer()
###################################################管理页面####################################################################
@ui.page("/manage")#需微调
def p_manage():
    def add_user():
        c.execute('insert into user (logid,name,password,iconpath,role) values(?,?,?,?,?)',("a11","","s8chhd23x","pic/user.png","用户"))
        sq.commit()
        fresh_user()
        user_grid.update()
    def add_book():
        c.execute('insert into book (name,aid,tid,press,pressdate,imgpath,score,intro) values(?,?,?,?,?,?,?,?)',("",1,0,"press","date","pic/book.png","scor","intro"))
        sq.commit()
        fresh_book()
        book_grid.update()
    def add_author():
        c.execute('insert into author (name,iconpath,intro,country) values(?,?,?,?)',("","pic/author.png","",""))
        sq.commit()
        fresh_author()
        author_grid.update()
    def add_read():
        c.execute('insert into read (uid,bid,state,stime,etime) values(?,?,?,?,?)',(1,1,"","NA","NA"))
        sq.commit()
        fresh_read()
        read_grid.update()
    def add_comm():
        c.execute('insert into comment (bid,content,time,uid) values(?,?,?,?)',(1,"","NA",1))
        sq.commit()
        fresh_comm()
        comm_grid.update()
    async def del_user():
        select=await user_grid.get_selected_row()
        if select :
            c.execute('delete from user where id=?',(select['id'],))
            sq.commit()
            fresh_user()
            user_grid.update()
        else:
            ui.notify("没有选择任何行！")
    async def del_book():
        select=await book_grid.get_selected_row()
        if select :
            c.execute('delete from book where id=?',(select['id'],))
            sq.commit()
            fresh_book()
            book_grid.update()
        else:
            ui.notify("没有选择任何行！")
    async def del_author():
        select=await author_grid.get_selected_row()
        if select :
            c.execute('delete from author where id=?',(select['id'],))
            sq.commit()
            fresh_author()
            author_grid.update()
        else:
            ui.notify("没有选择任何行！")
    async def del_read():
        select=await read_grid.get_selected_row()
        if select :
            c.execute('delete from read where id=?',(select['id'],))
            sq.commit()
            fresh_read()
            read_grid.update()
        else:
            ui.notify("没有选择任何行！")
    async def del_comm():
        select=await comm_grid.get_selected_row()
        if select :
            c.execute('delete from comment where id=?',(select['id'],))
            sq.commit()
            fresh_comm()
            comm_grid.update()
        else:
            ui.notify("没有选择任何行！")
    async def update_user():
        select=await user_grid.get_selected_row()
        if select :
            c.execute('update user set logid=?,name=?,password=?,iconpath=?,role=? where id=?',(select['登录账号'],select['用户名称'],select['用户密码'],select['头像路径'],select['用户权限'],select['id']))
            sq.commit()
            fresh_user()
            user_grid.update()
        else:
            ui.notify("没有选择任何行！")
    async def update_book():
        select=await book_grid.get_selected_row()
        if select :
            c.execute('update book set name=?,press=?,pressdate=?,imgpath=?,score=?,intro=? where id=?',(select['书名'],select['出版社'],select['出版日期'],select['封面'],select['评分'],select['简介'],select['id']))
            sq.commit()
            fresh_book()
            book_grid.update()
        else:
            ui.notify("没有选择任何行！")
    async def update_author():
        select=await author_grid.get_selected_row()
        if select :
            c.execute('update author set name=?,iconpath=?,intro=?,country=? where id=?',(select['姓名'],select['照片'],select['介绍'],select['国家'],select['id']))
            sq.commit()
            fresh_author()
            author_grid.update()
        else:
            ui.notify("没有选择任何行！")
    async def update_read():
        select=await read_grid.get_selected_row()
        if select :
            c.execute('update read set state=?,stime=?,etime=? where id=?',(select['阅读状态'],select['开始时间'],select['结束时间'],select['id']))
            sq.commit()
            fresh_read()
            read_grid.update()
        else:
            ui.notify("没有选择任何行！")
    async def update_comm():
        select=await comm_grid.get_selected_row()
        if select :
            c.execute('update comment set content=? where id=?',(select['评论内容'],select['id']))
            sq.commit()
            fresh_comm()
            comm_grid.update()
        else:
            ui.notify("没有选择任何行！")
    def fresh_user():
        rows.clear()
        c.execute('select * from user')
        users=c.fetchall()
        for user in users:
            x={
            'id': user[0],
            '登录账号': user[1],
            '用户名称': user[2],
            '用户密码': user[3],
            '头像路径': user[4],
            '用户权限': user[5]
            }
            rows.append(x)
    def fresh_book():
        a_list=[]
        c.execute('select * from author')
        authors=c.fetchall()
        for author in authors:
            a_list.append([author[0],author[1]])
        book_row.clear()
        c.execute('select * from book')
        books=c.fetchall()
        for book in books:
            aname="NA"
            tname="NA"
            for a in a_list:
                if a[0]==book[2]:
                    aname=a[1]
                if a[0]==book[3]:
                    tname=a[1]
            x={
            'id': book[0],
            '书名': book[1],
            '作者': aname,
            '译者': tname,
            '出版社': book[4],
            '出版日期': book[5],
            '封面': book[6],
            '评分': book[7],
            '简介': book[8],
            }
            book_row.append(x)
    def fresh_author():
        author_row.clear()
        c.execute('select * from author')
        authors=c.fetchall()
        for author in authors:
            x={
            'id': author[0],
            '姓名': author[1],
            '照片': author[2],
            '介绍': author[3],
            '国家': author[4],
            }
            author_row.append(x)
    def fresh_read():
        b_list=[]
        c.execute('select * from book')
        books=c.fetchall()
        for book in books:
            b_list.append([book[0],book[1]])
        read_row.clear()
        c.execute('select * from read')
        reads=c.fetchall()
        for read in reads:
            bname="NA"
            for b in b_list:
                if b[0]==read[2]:
                    bname=b[1]
            x={
            'id': read[0],
            '用户名称': read[1],
            '书籍信息': bname,
            '阅读状态': read[3],
            '开始时间': read[4],
            '结束时间': read[5],
            }
            read_row.append(x)
    def fresh_comm():
        b_list=[]
        c.execute('select * from book')
        books=c.fetchall()
        for book in books:
            b_list.append([book[0],book[1]])
        comm_row.clear()
        c.execute('select * from comment')
        comms=c.fetchall()
        for comm in comms:
            bname="NA"
            for b in b_list:
                if b[0]==comm[1]:
                    bname=b[1]
            x={
            'id': comm[0],
            '书籍信息': bname,
            '评论内容': comm[2],
            '评论时间': comm[3],
            '评论用户': comm[4],
            }
            comm_row.append(x)
    if app.storage.user.get("role")!="管理员" or app.storage.user.get("role") is None:
        ui.navigate.to("/login")
    else:
        ui.button("底层管理",on_click=lambda:ui.navigate.to('/deep')).style("width:99%")
        ui.button("书籍收录",on_click=lambda:ui.navigate.to('/include')).style("width:99%")
        with ui.card().style("width:100%;height:100%;padding:0px").props('outlined').props('square').props('elevation:10'):
            ui.label("用户管理").style("font-weight:bold;text-align:left;margin:5px")
            columns=[
            {'field': 'id'},
            {'field': '登录账号'},
            {'field': '用户名称'},
            {'field': '用户密码'},
            {'field': '头像路径'},
            {'field': '用户权限'}
            ]
            # 初始化表格数据列表
            rows=[]
            fresh_user()
            user_grid=ui.aggrid({
                'columnDefs': columns,
                'rowData': rows,
                'rowSelection':'single',
                'pagination':True,
                'paginationPageSize':7,
                'defaultColDef':{'editable': True},
                }
            )
            with ui.row().style("width:100%"):
                ui.button("新增",on_click=add_user).style("width:31%")
                ui.button("更新",on_click=update_user).style("width:31%")
                ui.button("删除",on_click=del_user).style("width:31%")
        with ui.card().style("width:100%;height:100%;padding:0px").props('outlined').props('square').props('elevation:10'):
            ui.label("记录管理").style("font-weight:bold;text-align:left;margin:5px")
            read_col=[
            {'field': 'id'},
            {'field': '用户名称'},
            {'field': '书籍信息'},
            {'field': '阅读状态'},
            {'field': '开始时间'},
            {'field': '结束时间'}
            ]
            # 初始化表格数据列表
            read_row=[]
            fresh_read()
            read_grid=ui.aggrid({
                'columnDefs': read_col,
                'rowData': read_row,
                'rowSelection':'single',
                'pagination':True,
                'paginationPageSize':7,
                'defaultColDef':{'editable': True},
                }
            )
            with ui.row().style("width:100%"):
                #ui.button("新增",on_click=add_read).style("width:31%")
                ui.button("更新",on_click=update_read).style("width:31%")
                ui.button("删除",on_click=del_read).style("width:31%")
        with ui.card().style("width:100%;height:100%;padding:0px").props('outlined').props('square').props('elevation:10'):
            ui.label("评论管理").style("font-weight:bold;text-align:left;margin:5px")
            comm_col=[
            {'field': 'id'},
            {'field': '书籍信息'},
            {'field': '评论内容'},
            {'field': '评论时间'},
            {'field': '评论用户'}
            ]
            # 初始化表格数据列表
            comm_row=[]
            fresh_comm()
            comm_grid=ui.aggrid({
                'columnDefs': comm_col,
                'rowData': comm_row,
                'rowSelection':'single',
                'pagination':True,
                'paginationPageSize':7,
                'defaultColDef':{'editable': True},
                }
            )
            with ui.row().style("width:100%"):
                #ui.button("新增",on_click=add_comm).style("width:31%")
                ui.button("更新",on_click=update_comm).style("width:31%")
                ui.button("删除",on_click=del_comm).style("width:31%")
        with ui.card().style("width:100%;height:100%;padding:0px").props('outlined').props('square').props('elevation:10'):
            ui.label("书籍管理").style("font-weight:bold;text-align:left;margin:5px")
            book_col=[
            {'field': 'id'},
            {'field': '书名'},
            {'field': '作者'},
            {'field': '译者'},
            {'field': '出版社'},
            {'field': '出版日期'},
            {'field': '封面'},
            {'field': '评分'},
            {'field': '简介'}
            ]
            # 初始化表格数据列表
            book_row=[]
            fresh_book()
            book_grid=ui.aggrid({
                'columnDefs': book_col,
                'rowData': book_row,
                'rowSelection':'single',
                'pagination':True,
                'paginationPageSize':7,
                'defaultColDef':{'editable': True},
                }
            )
            with ui.row().style("width:100%"):
                #ui.button("新增",on_click=add_book).style("width:31%")
                ui.button("更新",on_click=update_book).style("width:31%")
                ui.button("删除",on_click=del_book).style("width:31%")
        with ui.card().style("width:100%;height:100%;padding:0px").props('outlined').props('square').props('elevation:10'):
            ui.label("作者管理").style("font-weight:bold;text-align:left;margin:5px")
            author_col=[
            {'field': 'id'},
            {'field': '姓名'},
            {'field': '照片'},
            {'field': '介绍'},
            {'field': '国家'}
            ]
            # 初始化表格数据列表
            author_row=[]
            fresh_author()
            author_grid=ui.aggrid({
                'columnDefs': author_col,
                'rowData': author_row,
                'rowSelection':'single',
                'pagination':True,
                'paginationPageSize':7,
                'defaultColDef':{'editable': True},
                }
            )
            with ui.row().style("width:100%"):
                ui.button("新增",on_click=add_author).style("width:31%")
                ui.button("更新",on_click=update_author).style("width:31%")
                ui.button("删除",on_click=del_author).style("width:31%")
        footer()
###################################################底部导航####################################################################
def footer():
    ui.colors(primary=prime_color)
    with ui.footer().style('background-color:{}; display: flex; justify-content: space-around; align-items: center; padding: 10px;'.format(FOOT_COLOR)):
        with ui.row().style("margin:0px;padding:0px").on("click", lambda: ui.navigate.to('/store')):
            ui.avatar(icon='book',color=FOOT_COLOR,font_size="50px",square=True)
        with ui.row().style("margin:0px;padding:0px").on("click", lambda: ui.navigate.to('/')):
            ui.avatar(icon='home',color=FOOT_COLOR,font_size="50px",square=True)
        with ui.row().style("margin:0px;padding:0px").on("click", lambda: ui.navigate.to('/manage')):
            ui.avatar(icon='settings',color=FOOT_COLOR,font_size="50px",square=True)
def get_dominant_color(image_path):
    # 打开图像文件
    image = Image.open(image_path).convert('RGB')
    
    # 将图像转换为NumPy数组
    image_array = np.array(image)
    
    # 计算每个颜色通道的平均值
    shape = image_array.shape
    height, width, channels = shape
    size = height * width
    
    # 计算每个颜色通道的总和
    sum_r = np.sum(image_array[:, :, 0])
    sum_g = np.sum(image_array[:, :, 1])
    sum_b = np.sum(image_array[:, :, 2])
    
    # 计算每个颜色通道的平均值
    avg_r = sum_r / size
    avg_g = sum_g / size
    avg_b = sum_b / size
    # 返回主色（RGB格式）


    ts="font-weight: bold;color:rgba({},{},{},{});font-size:15px".format(255-avg_r,255-avg_g,255-avg_b,1)
    fs="font-weight: bold;color:rgba({},{},{},{});font-size:10px".format(255-avg_r,255-avg_g,255-avg_b,0.8)
    return "background-color:rgba({},{},{},{})".format(avg_r,avg_g,avg_b,0.5),ts,fs
ui.run(storage_secret="feixiang",port=3032)